package com.sea.finance.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;

public class ExcelToMySQLImporter {
    private static final String[] HEADERS = {
            "settlement_id", "settlement_start_date", "settlement_end_date",
            "deposit_date", "total_amount", "currency", "transaction_type",
            "order_id", "merchant_order_id", "adjustment_id", "shipment_id",
            "marketplace_name", "amount_type", "amount_description", "amount",
            "fulfillment_id", "posted_date", "posted_date_time", "order_item_code",
            "merchant_order_item_id", "merchant_adjustment_item_id", "sku",
            "quantity_purchased", "promotion_id"
    };

    private static final String JDBC_URL = "jdbc:mysql://rm-bp1f55u0b1x66n0ws8o.mysql.rds.aliyuncs.com:3306/shinebed_pro?useSSL=false&rewriteBatchedStatements=true&useServerPrepStmts=true&cachePrepStmts=true";
    private static final String USER = "ykjdata";
    private static final String PASS = "chf_19960801";
    private static final int BATCH_SIZE = 5000;

    public static void main(String[] args) {
        long startTime = System.currentTimeMillis();

        try {
            URL url = new URL("http://cozylux-v2.oss-cn-beijing.aliyuncs.com/invoice%206.06-6.20.zip?Expires=3331764409&OSSAccessKeyId=LTAI4G55LJEk8nARtrjYngou&Signature=ugVV09BjH565hoIhbnwYG9HfqAg%3D");
            processZipFile(url);
        } catch (Exception e) {
            e.printStackTrace();
        }

        System.out.println("导入完成，耗时: " + (System.currentTimeMillis() - startTime)/1000 + "秒");
    }

    private static void processZipFile(URL url) throws Exception {
        try (InputStream is = url.openStream();
             ZipInputStream zis = new ZipInputStream(is);
             Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASS)) {

            conn.setAutoCommit(false);
            ZipEntry entry;
            while ((entry = zis.getNextEntry()) != null) {
                if (!entry.getName().endsWith(".xlsx")) continue;

                // 将ZIP条目内容读取到内存中
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                byte[] buffer = new byte[1024];
                int len;
                while ((len = zis.read(buffer)) > -1) {
                    baos.write(buffer, 0, len);
                }
                baos.flush();

                // 使用内存中的数据进行处理
                processExcelData(new ByteArrayInputStream(baos.toByteArray()), conn);
            }
            conn.commit();
        }
    }

    private static void processExcelData(InputStream excelStream, Connection conn) throws Exception {
        String sql = "INSERT INTO a_vt (" + String.join(", ", HEADERS) + ") VALUES (" +
                String.join(", ", Collections.nCopies(HEADERS.length, "?")) + ")";

        // 添加计数器
        AtomicInteger counter = new AtomicInteger(0);
        long startTime = System.currentTimeMillis();

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            EasyExcel.read(excelStream, new PageReadListener<Map<Integer, String>>(dataList -> {
                try {
                    for (Map<Integer, String> data : dataList) {
                        for (int i = 0; i < HEADERS.length; i++) {
                            pstmt.setObject(i + 1, data.get(i));
                        }
                        pstmt.addBatch();

                        // 更新计数器并打印进度
                        int current = counter.incrementAndGet();
                        if (current % 1000 == 0) {  // 每1000条打印一次
                            long elapsed = (System.currentTimeMillis() - startTime) / 1000;
                            System.out.printf("已导入: %,d 条, 耗时: %d秒, 当前速度: %,d 条/秒%n",
                                    current, elapsed, current/Math.max(1, elapsed));
                        }
                    }
                    pstmt.executeBatch();
                } catch (SQLException e) {
                    throw new RuntimeException("数据库操作失败", e);
                }
            }, BATCH_SIZE)).sheet().doRead();

            // 导入完成后打印总结
            System.out.printf("导入完成! 总计导入: %,d 条数据%n", counter.get());
        }
    }
}